package com.feeyin.radar.commons;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
  
public class ExcelReader {
	
    private POIFSFileSystem fs;   
    private HSSFWorkbook wb;   
    private HSSFSheet sheet;   
    private HSSFRow row;   
    private FileInputStream input;   
    private String[] excleTitle;
    
    public String[] readExcelTitle(String excelPath) {// 读取Excel表格表头的内容   
        try {   
            input = new FileInputStream(new File(excelPath));// excelPath,Excel   
            // 文件 的绝对路径   
            fs = new POIFSFileSystem(input);   
            wb = new HSSFWorkbook(fs);   
            sheet = wb.getSheetAt(0);   
            row = sheet.getRow(0);// 得到标题的内容对象。   
            int colNum = row.getPhysicalNumberOfCells(); // 得到标题总列数   
            excleTitle = new String[colNum];   
            for (int i = 0; i < colNum; i++) {   
                excleTitle[i] = getStringCellValue(row.getCell((short) i));   
            }   
        } catch (FileNotFoundException e) {   
            e.printStackTrace();   
        } catch (IOException e) {   
            e.printStackTrace();   
        } finally {   
            try {   
                if (input != null) {   
                    input.close();   
                }   
            } catch (IOException e) {   
                e.printStackTrace();   
            }   
        }   
        return excleTitle;   
    }   
//    public Set readExcelContent(String excelPath) {// 读取Excel数据内容   
//        Map content = new HashMap();   
//        String excelStr = "";// excel 内容
//        List list = new ArrayList();
//        Set set = new HashSet();
//        
//        try {   
//            input = new FileInputStream(new File(excelPath));   
//            fs = new POIFSFileSystem(input);   
//            wb = new HSSFWorkbook(fs);   
//            sheet = wb.getSheetAt(0);   
//            int rowNum = sheet.getLastRowNum(); // 得到总行数   
//            row = sheet.getRow(0);// 得到标题的内容对象。   
//            int colNum = row.getPhysicalNumberOfCells();// 得到每行的列数。   
//            for (int i = 1; i <= rowNum; i++) { // 正文内容应该从第二行开始,第一行为表头的标题 
//            	String[] rowdatas = new String[colNum];
//                row = sheet.getRow(i);   
//                int j = 0;   
//                while (j < colNum) { 
//                	rowdatas[j] = getStringCellValue(row.getCell((short) j)).trim();
//                    excelStr += getStringCellValue(row.getCell((short) j)).trim()+ "&";   
//                    j++;   
//                }
//                list.add(rowdatas);
//                content.put(i, excelStr);   
//                excelStr = "";   
//            }   
//        } catch (FileNotFoundException e) {   
//            e.printStackTrace();   
//        } catch (IOException e) {   
//            e.printStackTrace();   
//        } finally {   
//            try {   
//                if (input != null) {   
//                    input.close();   
//                }   
//            } catch (IOException e) {   
//                e.printStackTrace();   
//            }   
//        }   
//        return set;   
//    }
    
    public Map readExcelContent(String excelPath) {// 读取Excel数据内容 
    	
        Map content = new HashMap();   
        String excelStr = "";// excel 内容
        String str0 = "";
        
        try {   
            input = new FileInputStream(new File(excelPath));   
            fs = new POIFSFileSystem(input);   
            wb = new HSSFWorkbook(fs);   
            sheet = wb.getSheetAt(0);   
            int rowNum = sheet.getLastRowNum(); // 得到总行数   
            row = sheet.getRow(0);// 得到标题的内容对象。   
            int colNum = row.getPhysicalNumberOfCells();// 得到每行的列数。   
            for (int i = 1; i <= rowNum; i++) { // 正文内容应该从第二行开始,第一行为表头的标题 
                row = sheet.getRow(i);   
                int j = 0;   
                while (j < colNum) { 
                	if(getStringCellValue(row.getCell((short) j)).trim() != null 
                			&& !"".equals(getStringCellValue(row.getCell((short) j)).trim())){
                		if(j == 15){
                			excelStr += getDateCellValue(row.getCell((short) j)).trim() + "    ";
                		}else{
                			excelStr += getStringCellValue(row.getCell((short) j)).trim() + "    ";
                		}
                	}
                    j++;   
                }
                if(excelStr != null && !"".equals(excelStr)){
                	System.out.println("第行"+i+"导入成功！");
                }
                content.put(i, excelStr);
                System.out.println(excelStr);
                excelStr = "";   
            }   
        } catch (FileNotFoundException e) {   
            e.printStackTrace();   
        } catch (IOException e) {   
            e.printStackTrace();   
        } finally {   
            try {   
                if (input != null) {   
                    input.close();   
                }   
            } catch (IOException e) {   
                e.printStackTrace();   
            }   
        }   
        return content;   
    }  
    
    private String getStringCellValue(HSSFCell cell) {// 获取单元格数据内容为字符串类型的数据   
        String strCell = "";
        if(cell == null)return "";
        switch (cell.getCellType()) {   
        case HSSFCell.CELL_TYPE_STRING:   
            strCell = cell.getStringCellValue();   
            break;   
        case HSSFCell.CELL_TYPE_NUMERIC:   //Double.valueOf(cell.getNumericCellValue())
//            strCell = String.valueOf(cell.getNumericCellValue());   
        	strCell = Double.valueOf(cell.getNumericCellValue()).longValue() + "";
            break;   
        case HSSFCell.CELL_TYPE_BOOLEAN:   
            strCell = String.valueOf(cell.getBooleanCellValue());   
            break; 
        case HSSFCell.CELL_TYPE_BLANK:   
            strCell = "";   
            break;   
        default:   
            strCell = "";   
            break;   
        }   
        if (strCell.equals("") || strCell == null) {   
            return "";   
        }   
        if (cell == null) {   
            return "";   
        }   
        return strCell;   
    }   
    private String getDateCellValue(HSSFCell cell) {// 获取单元格数据内容为日期类型的数据   
        String result = "";   
        try {   
            int cellType = cell.getCellType();   
            if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {   
                Date date = cell.getDateCellValue();   
                result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)   
                        + "-" + date.getDate();   
            } else if (cellType == HSSFCell.CELL_TYPE_STRING) {   
                String date = getStringCellValue(cell);   
                result = date.replaceAll("[年月]", "-").replace("日", "").trim();   
            } else if (cellType == HSSFCell.CELL_TYPE_BLANK) {   
                result = "";   
            }   
        } catch (Exception e) {   
            System.out.println("日期格式不正确!");   
            e.printStackTrace();   
        }   
        return result;   
    }
    
//    public boolean isRepeat(Set set,String[] ds){
//    	boolean result = false;
//		Iterator it = set.iterator();
//		while(it.hasNext()){
//			String[] datas = new String[set.size()];
//			datas = (String[])it.next();
//			for(int j = 0; j < datas.length; j++){
//				if(datas[j].equals(ds[j])){
//					result = true;
//					break;
//				}
//			}
//		}
//		return result;
//	}
    
    public void writeExcel(){
		FileOutputStream fos;
		try {
			fos = new FileOutputStream("f:/test.xls");
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet s = wb.createSheet();
			wb.setSheetName(0, "sheet1");
			
			//table head
			HSSFRow row = s.createRow(0);
			
			HSSFFont fontStyle = wb.createFont(); 
//			fontStyle.setFontName("宋体"); 
			fontStyle.setFontHeightInPoints((short)12); 
			fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
			
			HSSFCellStyle cellStyle = wb.createCellStyle();
			cellStyle.setFillForegroundColor(HSSFColor.BLUE.index);
			cellStyle.setFont(fontStyle);
			cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			
			s.setDefaultColumnWidth((short)18);
			
			HSSFCell cell = row.createCell((short)0);
			cell.setCellStyle(cellStyle);
			cell.setCellValue("合同编码");
			cell.setCellStyle(cellStyle);
			cell = row.createCell((short)1);
			cell.setCellValue("合同名称");
			cell.setCellStyle(cellStyle);
			cell = row.createCell((short)2);
			cell.setCellValue("应收帐款(元)");
			cell.setCellStyle(cellStyle);
			cell = row.createCell((short)3);
			cell.setCellStyle(cellStyle);
			cell.setCellValue("建账日期");
			cell = row.createCell((short)4);
			cell.setCellStyle(cellStyle);
			cell.setCellValue("账龄(天)");
			cell = row.createCell((short)5);
			cell.setCellStyle(cellStyle);
			cell.setCellValue("销售代表");
			
			for(int i = 1; i < 10; i++){//rows
				row = s.createRow(i);
				for(int j = 0; j < 6; j++){//cols
					cell = row.createCell((short)j);
					cell.setCellValue("测试用例"+j);
				}
			}
			wb.write(fos);
			fos.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
    
    public static void main(String[] args) {
		ExcelReader er = new ExcelReader();
		er.writeExcel();
	}
} 